package com.example.excel.controller;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.example.excel.dto.StockCalDTO;
import com.example.excel.dto.StockDTO;
import com.example.excel.entity.Stock;
import com.example.excel.dao.StockRepository;
import com.example.excel.listener.CustomEventListener;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.IndexedColorMap;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;;
import org.springframework.web.bind.annotation.RequestPart;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.rmi.CORBA.Util;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.util.*;

@RestController
@RequestMapping
@Slf4j
public class StockController {

    @Autowired
    private StockRepository stockRepository;

    @PostMapping("/importExcel")
    @ApiOperation(value = "导入excel", notes = "导入excel")
    //@Log(operation = "更新或者添加行李报价", businessType = BusinessType.UPDATE)
    public void importExcel(@RequestPart("file") MultipartFile file, Integer saleNum, Float priceSingle, HttpServletResponse response) throws IOException {
        List<StockDTO> stockList = new ArrayList<>();
        ReadListener<Date> listener = new CustomEventListener();
        try {
            // 获取数据
            EasyExcel.read(file.getInputStream(), StockDTO.class, new AnalysisEventListener<StockDTO>() {
                @Override
                public void invoke(StockDTO data, AnalysisContext context) {
                    stockList.add(data);
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    log.info("读取完毕");
                }
            }).doReadAll();
        } catch (Exception e) {
            log.info("导入异常：", e);
            throw new RuntimeException("导入异常");
        }
        if (saleNum == null) {
            saleNum = 0;
        }
        //异步跑
        int upCount = 0;
        int downCount = 0;
        Double sumUp = 0d;
        Double sumDown = 0D;
        Double sum = 0d;
        //平盘
        Double line = 0d;
        List<StockCalDTO> res = new ArrayList<>();
        for (StockDTO stock : stockList) {
           /* String time = stock.getTime().replaceAll("\t", " ");
            if(time.contains(" ")){
                String[] split = time.split(" ");
                stock.setTime(split[0]);
                stock.setPrice(Double.parseDouble(split[1]));
                stock.setSaleNumStr(split[2]);
                stock.setNum(Integer.parseInt(split[3]));
            }*/


            String saleNum1 = stock.getSaleNumStr();
            if (!saleNum1.contains("↓") && !saleNum1.contains("↑")) {
                int i = 0;
                try {
                    i = Integer.parseInt(saleNum1.replace("--", ""));
                } catch (NumberFormatException e) {
                    continue;
                }
                if (Math.abs(i) < saleNum) {
                    continue;
                }
                StockCalDTO stock1 = BeanUtil.copyProperties(stock, StockCalDTO.class);
                stock1.setSaleNumStr(saleNum1);
                stock1.setPriceTotal(null);
                stock1.setPriceSignal(null);
                res.add(stock1);
                line += i * stock.getPrice();
                continue;
            }
            int saleNum2 = 0;
            if (saleNum1.contains("↑")) {
                saleNum2 = Integer.parseInt(saleNum1.replace("↑", ""));
            } else {
                saleNum2 = -Integer.parseInt(saleNum1.replace("↓", ""));
            }
            if (Math.abs(saleNum2) < saleNum) {
                continue;
            }
            if (saleNum2 > 0) {
                upCount++;
            } else {
                downCount++;
            }

            DateTime parse = DateUtil.parse(stock.getTime(), "H:mm");
            DateTime dateTime = DateUtil.offsetMinute(parse, 6);
            stock.setTime(dateTime.toString("H:mm"));
           /* boolean b = stock.getTime().length() == 4;
            if (b) {
                DateTime date = DateUtil.date();
                stock.setTime(date.toDateStr() + stock.getTime());
            }*/

            StockCalDTO stock1 = BeanUtil.copyProperties(stock, StockCalDTO.class);
            stock1.setSaleNumStr(saleNum1);
            stock1.setPriceTotal(stock1.getPrice() * saleNum2);
            stock1.setPriceSignal(String.format("%.2f", stock1.getPriceTotal() / stock1.getNum()));
            res.add(stock1);

            if (stock1.getPriceTotal() > 0) {
                sumUp += stock1.getPriceTotal();
            } else {
                sumDown += stock1.getPriceTotal();
            }
            sum += stock1.getPriceTotal();
            //stockRepository.save(stock1);
        }

        Double total = sum + line;

        ExportParams exportParams = new ExportParams();
        HSSFWorkbook workbook = (HSSFWorkbook) ExcelExportUtil.exportExcel(exportParams, StockCalDTO.class, res);

        Sheet sheet = workbook.getSheetAt(0);

        Map<Short, CellStyle> map = new HashMap<>();

        CellStyle cs2 = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cs2.setDataFormat(createHelper.createDataFormat().getFormat("#,##0"));
        cs2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs2.setAlignment(HorizontalAlignment.CENTER);
        cs2.setVerticalAlignment(VerticalAlignment.CENTER);
        cs2.setBorderBottom(BorderStyle.THIN);  // 设置下边框为细线
        cs2.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置下边框颜色为黑色
        cs2.setBorderTop(BorderStyle.THIN);  // 设置上边框为细线
        cs2.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置上边框颜色为黑色
        cs2.setBorderLeft(BorderStyle.THIN);  // 设置左边框为细线
        cs2.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置左边框颜色为黑色
        cs2.setBorderRight(BorderStyle.THIN);  // 设置右边框为细线
        cs2.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置右边框颜色为黑色


        CellStyle cs3 = workbook.createCellStyle();
        cs3.setDataFormat(createHelper.createDataFormat().getFormat("#,##0"));
        cs3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs3.setAlignment(HorizontalAlignment.CENTER);
        cs3.setVerticalAlignment(VerticalAlignment.CENTER);
        cs3.setBorderBottom(BorderStyle.THIN);  // 设置下边框为细线
        cs3.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置下边框颜色为黑色
        cs3.setBorderTop(BorderStyle.THIN);  // 设置上边框为细线
        cs3.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置上边框颜色为黑色
        cs3.setBorderLeft(BorderStyle.THIN);  // 设置左边框为细线
        cs3.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置左边框颜色为黑色
        cs3.setBorderRight(BorderStyle.THIN);  // 设置右边框为细线
        cs3.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置右边框颜色为黑色

        int rowIndex = 2;
        // 将插入行后的所有行下移
        for (int i = sheet.getLastRowNum(); i >= 0; i--) {
            Cell cell = sheet.getRow(i).getCell(4);
            String stringCellValue = cell.getStringCellValue();
            if (stringCellValue == null) {
                continue;
            }
            double cellValue = 0;
            try {
                cellValue = Double.parseDouble(stringCellValue);
            } catch (NumberFormatException e) {
                sheet.shiftRows(i, i, rowIndex, true, false);
                continue;
            }
            short index;
            cell.setCellValue(cellValue);
            if (cellValue > 0) {
                index = 9;
                HSSFPalette customPalette = workbook.getCustomPalette();
                customPalette.setColorAtIndex(index, (byte) 244, (byte) 176, (byte) 132);

                CellStyle cs = map.get(index);
                if (cs == null) {
                    cs = getCellStyle(workbook);
                    map.put(index, cs);
                }

                // XSSFColor color = new XSSFColor(new java.awt.Color(255, 189, 189));
                cs.setFillForegroundColor(index);
                cell.setCellStyle(cs);
            } else {
                index =  10;
                HSSFPalette customPalette = workbook.getCustomPalette();
                customPalette.setColorAtIndex(index, (byte) 217, (byte) 225, (byte) 242);

                CellStyle cs = map.get(index);
                if (cs == null) {
                    cs = getCellStyle(workbook);
                    map.put(index, cs);
                }

                cs.setFillForegroundColor(index); // 设置前景颜色为黄色
                cell.setCellStyle(cs);
            }
            Cell cell2 = sheet.getRow(i).getCell(5);
            float parseFloat = Float.parseFloat(cell2.getStringCellValue());
            cell2.setCellValue(parseFloat);
            if(index==9){
                cs2.setFillForegroundColor(index);
                if (priceSingle != null && Math.abs(parseFloat) > priceSingle) {
                    Font font = workbook.createFont();
                    font.setColor(IndexedColors.DARK_RED.getIndex()); // 设置字体颜色为红色
                    cs2.setFont(font);
                }
                cell2.setCellStyle(cs2);
            }else{
                cs3.setFillForegroundColor(index);
                if (priceSingle != null && Math.abs(parseFloat) > priceSingle) {
                    Font font = workbook.createFont();
                    font.setColor(IndexedColors.DARK_RED.getIndex()); // 设置字体颜色为红色
                    cs3.setFont(font);
                }
                cell2.setCellStyle(cs3);
            }
            sheet.shiftRows(i, i, rowIndex, true, false);
        }

        CellStyle cell1Style = workbook.createCellStyle();
        cell1Style.setDataFormat(createHelper.createDataFormat().getFormat("#,##0"));
        cell1Style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        cell1Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell1Style.setAlignment(HorizontalAlignment.CENTER);
        cell1Style.setVerticalAlignment(VerticalAlignment.CENTER);
        cell1Style.setBorderBottom(BorderStyle.THIN);  // 设置下边框为细线
        cell1Style.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置下边框颜色为黑色
        cell1Style.setBorderTop(BorderStyle.THIN);  // 设置上边框为细线
        cell1Style.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置上边框颜色为黑色
        cell1Style.setBorderLeft(BorderStyle.THIN);  // 设置左边框为细线
        cell1Style.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置左边框颜色为黑色
        cell1Style.setBorderRight(BorderStyle.THIN);  // 设置右边框为细线
        cell1Style.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置右边框颜色为黑色

        Row row = sheet.createRow(0); // 创建第一行
        row.setHeightInPoints(30);
        Cell cell = row.createCell(0);
        cell.setCellValue("上涨次数合计");
        cell.setCellStyle(cell1Style);
        cell = row.createCell(1);
        cell.setCellValue("下跌次数合计");
        cell.setCellStyle(cell1Style);
        cell = row.createCell(2);
        cell.setCellValue("上涨金额合计");
        cell.setCellStyle(cell1Style);

        cell = row.createCell(3);
        cell.setCellValue("下跌金额合计");
        cell.setCellStyle(cell1Style);

        cell = row.createCell(4);
        cell.setCellValue("上涨下跌差");
        cell.setCellStyle(cell1Style);

        cell = row.createCell(5);
        cell.setCellValue("平盘");
        cell.setCellStyle(cell1Style);

        cell = row.createCell(6);
        cell.setCellValue("成交总金额合计");
        cell.setCellStyle(cell1Style);


        row = sheet.createRow(1); // 创建第2行
        row.setHeightInPoints(30);
        cell = row.createCell(0);
        cell.setCellValue(upCount);
        cell.setCellStyle(cell1Style);
        cell = row.createCell(1);
        cell.setCellValue(downCount);
        cell.setCellStyle(cell1Style);
        cell = row.createCell(2);
        cell.setCellValue(sumUp);
        cell.setCellStyle(cell1Style);

        cell = row.createCell(3);
        cell.setCellValue(sumDown);
        cell.setCellStyle(cell1Style);

        cell = row.createCell(4);
        cell.setCellValue(sum);
        cell.setCellStyle(cell1Style);

        cell = row.createCell(5);
        cell.setCellValue(line);
        cell.setCellStyle(cell1Style);

        cell = row.createCell(6);
        cell.setCellValue(total);
        cell.setCellStyle(cell1Style);

        sheet.createFreezePane(0, 3);
        sheet.setColumnWidth(6, 20 * 256);

        OutputStream os = null;

        String fileName = "统计报表.xls";
        // response.setHeader("Content-Disposition","attachment;filename*=UTF-8''"+ URLEncoder.encode(fileName,"UTF-8"));//指定下载的文件名

        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        response.setHeader("Content-Type", "application/vnd.ms-excel");

        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        int length = baos.size();
        response.addHeader("Content-Length", length + "");
        // 下载文件的默认名称(设置下载文件的默认名称)
        try {
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            os.close();
            workbook.close();
        }
    }

    private CellStyle getCellStyle(Workbook workbook) {
        CellStyle cs = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cs.setDataFormat(createHelper.createDataFormat().getFormat("#,##0"));
        //or
        //cs.setDataFormat((short) 7);
        cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cs.setAlignment(HorizontalAlignment.CENTER);
        cs.setVerticalAlignment(VerticalAlignment.CENTER);
        cs.setBorderBottom(BorderStyle.THIN);  // 设置下边框为细线
        cs.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置下边框颜色为黑色
        cs.setBorderTop(BorderStyle.THIN);  // 设置上边框为细线
        cs.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置上边框颜色为黑色
        cs.setBorderLeft(BorderStyle.THIN);  // 设置左边框为细线
        cs.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置左边框颜色为黑色
        cs.setBorderRight(BorderStyle.THIN);  // 设置右边框为细线
        cs.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());  // 设置右边框颜色为黑色
        return cs;
    }
}